package com.lingdian.fund.repository;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.lingdian.fund.bean.FundData;
import com.lingdian.fund.vo.FundVO;
@Repository("fundDataReporsitory")
public interface FundDataReporsitory extends CrudRepository<FundData, Long>{

	
	List<FundData> findByCodeAndFundDate(String code,String funDate);
	List<FundData> findByCodeOrderByFundDate(String code);
	List<FundData> findByCodeAndFundDateBetweenOrderByFundDate(String code,String begin,String end);
	List<FundData> findTop1ByCodeAndFundDateBetweenOrderByUnitValueAsc(String code,String begin,String end);
	List<FundData> findTop1ByCodeAndFundDateBetweenOrderByUnitValueDesc(String code,String begin,String end);
	List<FundData> findTop1ByCodeAndFundDateBetweenOrderByFundDateDesc(String code,String begin,String end);

	//@Query("SELECT new com.lingdian.fund.vo.FundDataVO(fd.code,MAX(fd.unitValue),min(fd.unitValue)) from FundData fd where fd.fundDate<=?1 group by fd.code")
/*	@Query("select new com.lingdian.fund.vo.FundDataVO(a.code,a.maxv,a.minv,b.tvalue) from "
			+ " ("
			+ " SELECT f1.code,MAX(f1.unitValue) as maxv ,min(f1.unitValue) as minv ,MAX(f1.unitValue)-min(f1.unitValue) as rangedata from FundData f1 where fundDate>=?1 group by f1.code"
			+ " )a,"
			+ " (SELECT unitValue as tvalue,code from FundData where fundDate=?2)b"
			+ " where a.code = b.code  ")*/
	@Query(value="select a.code as code ,a.maxv as maxUnitValue ,a.minv as minUnitValue,b.tvalue as nowUnitValue,(b.tvalue-a.minv) tt,b.name as name from "
			+ " ("
			+ " SELECT f1.code,MAX(f1.unit_value) as maxv ,min(f1.unit_value) as minv ,MAX(f1.unit_value)-min(f1.unit_value) as rangedata from fund_data f1 where fund_date<=?1 group by f1.code"
			+ " )a,"
			+ " (SELECT unit_value as tvalue,code,f.name from fund_data fd left join fund f on fd.`code`=f.id where fund_date=?2)b"
			+ " where a.code = b.code  ORDER BY tt,rangedata ",nativeQuery=true)
	List<Object[]> findData(String beginDate,String nowDate);

}
